package com.edu.test.reflect.edu05;

import cn.hutool.core.convert.Convert;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @作者 five-five
 * @创建时间 2021/2/17
 */
public class ExcelReader {
    /**
     * 根据文件输入流来读取excel的内容
     *
     * @param inputStream
     * @return
     */
    public static List<Map<String, Object>> getDataToMapList(InputStream inputStream, int sheetNo) throws IOException {
        List<Map<String, Object>> mapList = new ArrayList<>();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
//        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);//老版的office用这个
        //获取指定的sheet
        int numberOfSheets = xssfWorkbook.getNumberOfSheets();
        if (numberOfSheets < sheetNo) {
            return mapList;
        }
        XSSFSheet hssfSheet = xssfWorkbook.getSheetAt(sheetNo);

        // 循环行Row
        //拿出第一行的数据
        XSSFRow rowTitle = hssfSheet.getRow(0);
        short titleLength = rowTitle.getLastCellNum();
        String[] titles = new String[titleLength];
        for (int i = 0; i < titleLength; i++) {
            //储存起来
            titles[i] = getStringValue(rowTitle.getCell(i));
        }
        ;
        //从第二行开始
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            XSSFRow hssfRow = hssfSheet.getRow(rowNum);
            if (hssfRow == null) {
                continue;
            }
            // 循环列Cell
            Map<String, Object> map = new LinkedHashMap<>();
            for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
                XSSFCell xssfCell = hssfRow.getCell(cellNum);
                if (xssfCell == null) {
                    continue;
                }
//                System.out.print(titles[cellNum] +"\t"+ getStringValue(xssfCell));
                map.put(titles[cellNum], getStringValue(xssfCell));
            }
//            System.out.println();
            mapList.add(map);
        }
        return mapList;
    }

    /**
     * @param tClass
     * @param mapList
     * @param <T>
     * @return
     */
    public static <T> List<T> valuePoJO(Class<T> tClass, List<Map<String, Object>> mapList)
            throws IllegalAccessException, InstantiationException, NoSuchFieldException {
        List<T> list = new ArrayList<>();
        Field[] fields = tClass.getDeclaredFields();
        //key为列名，value为map
        Map<String, Field> preperPOJOMaps = new LinkedHashMap<>();
        if (fields != null && fields.length != 0) {
            for (Field field : fields) {
                //权限问题
                boolean accessible = field.isAccessible();
                if (!accessible) {
                    field.setAccessible(true);
                }
                //拿到注解
                CellKV annotation = field.getAnnotation(CellKV.class);
                String key = annotation.key();
                preperPOJOMaps.put(key,field);
            }
        }

        int i = 0;
        for (Map<String, Object> map : mapList) {
            Set<String> keySet = map.keySet();
            T target = tClass.newInstance();
            for (String key : keySet) {
                if (preperPOJOMaps.keySet().contains(key)){
                    //有这个列名
                    Field field1 = preperPOJOMaps.get(key);
                    //开始做数据处理
                    Field field = tClass.getDeclaredField(field1.getName().trim());
                    //授权
                    if (!field.isAccessible()) {
                        field.setAccessible(true);
                    }
                    field.set(target, Convert.convert(field1.getType(),map.get(key)));
                }else {
                    //计数器加一
                    i++;
                };
            }
            if (i == keySet.size()) {
                //一个都没有匹配上
                return list;
            }
            //添加
            list.add(target);
        }
        return list;
    }

    /**
     * 根据XSSFCell的toString方法进行仿造的
     *
     * @param xssfCell
     * @return
     */
    private static String getStringValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(xssfCell)) {
                DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                return sdf.format(xssfCell.getDateCellValue());
            }
            return String.valueOf(xssfCell.getNumericCellValue());
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

}
